import java.util.Date;
import java.util.Vector;
import java.sql.*;
import javax.swing.JOptionPane;

public class DatabaseAccess {
	private static final String username = "mwillz88";
	private static final String pass = "0620539";
	private static final String url = "jdbc:mysql://info340.ischool.washington.edu/airline_b_team2";
	
	/**
	 * 
	 * This method is finished, need to potentially modify city/iata stuff, awaiting email from Kevin
	 * 
	 */
	public static Airport[] GetAirportCities()
	{
		// TODO:  Query the database and retrive the information.
		// DONE
		// resultset.findcolumn(string col)
		Vector<Airport> vAirports = new Vector<Airport>();
		Connection conn;
		try{
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, username, pass);
			Statement s = conn.createStatement();
			s.executeQuery("SELECT iata, city FROM airport");
			ResultSet rs = s.getResultSet();
						
			Airport a;
			while(rs.next()){
				a = new Airport(rs.getString("iata"),rs.getString("city"));
				vAirports.add(a);
			}
			conn.close();
		}
		catch(Exception e){
			System.err.println(e.toString());
		}
		Airport[] arrAirport = new Airport[vAirports.size()];
		vAirports.toArray(arrAirport);
		return arrAirport;
	}
	
	/**
	 * 
	 * This method is finished
	 * 
	 */
	public static Flight[] GetFlights(Airport DepartAirport, Airport ArriveAirport, Date DepartureDate )
	{
		Vector<Flight> vFlights = new Vector<Flight>();
		// TODO:  Query the database and retrieve the information.
		// Loop through the row results, creating a new flight for each row.
		//  Add those flights to the vFlights vector.

		// DUMMY DATA FOLLOWS
		/*Flight f = new Flight();
		f.FlightID = 1;	// ID from the DB..
		f.ArrivalAirport = new Airport(1,"Seattle");
		f.DepartureAirport = new Airport(1,"Portland");
		f.ArrivalTime = new Date();
		f.DepartureTime = new Date();
		f.BasePrice = 150;
		f.Capacity = 10;
		f.CurrentPrice = 300;
		f.FlightNumber = "642";
		f.Reservations = null;	// Don't need to load these now.
	    */
		Connection conn;
		Flight f;
		try{
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, username, pass);
			PreparedStatement ps = conn.prepareStatement("select fd.*, (select city from airport where iata=fd.destid) as destcity, " +
					"(select city from airport where iata=fd.deptid) as deptcity, " +
					"capacity, bookingprice from flight_data fd " +
					"join (select flightinfo.flightid, (allseatcount-bookedseats) as capacity, " +
					"if(isnull(bookedseats/allseatcount),0,(bookedseats/allseatcount)) as booked_percent, baseprice, date_diff, " +
					"if(date_diff > 14,baseprice, " +
					"if(bookedseats/allseatcount >= 0.75, baseprice*2,if(bookedseats/allseatcount < 0.75 and bookedseats/allseatcount >= 0.5,baseprice,baseprice/2))) " +
					"as bookingprice from " +
					"(select f.flightid as flightid, count(seat) as allseatcount, baseprice, datediff(deptdatetime,curdate()) as date_diff " +
					"from seat_options s, flight_data f, plane p where s.planetype=p.planetype and p.planeid=f.planeid group by f.flightid) as flightinfo " +
					"left join (select f.flightid as flightid, count(*) as bookedseats from reservation r, flight_data f, plane p, plane_types t " +
					"where r.flightid=f.flightid and f.planeid=p.planeid and p.planetype=t.typeid group by flightid) as bookedtable " +
					"on flightinfo.flightid=bookedtable.flightid) as booking_info on booking_info.flightid=fd.flightid " +
					"having fd.deptid=? and fd.destid=? and deptdatetime between ? and ADDDATE(?, INTERVAL 1 DAY)");
			ps.setString(1, DepartAirport.AirportID);
			ps.setString(2, ArriveAirport.AirportID);
			ps.setDate(3, java.sql.Date.valueOf(DepartureDate.toString()));
			ps.setDate(4, java.sql.Date.valueOf(DepartureDate.toString()));
			
			ps.executeQuery();
			ResultSet rs = ps.getResultSet();
			while(rs.next()){
				
				f = new Flight();
				f.FlightID = rs.getInt("FlightId");
				f.ArrivalAirport = new Airport(rs.getString("DestId"),rs.getString("DestCity"));
				f.DepartureAirport = new Airport(rs.getString("DeptId"),rs.getString("DeptCity"));
				f.ArrivalTime = new Date(rs.getTimestamp("ArrDateTime").getTime());
				f.DepartureTime = new Date(rs.getTimestamp("DeptDateTime").getTime());
				f.BasePrice = rs.getFloat("BasePrice");
				f.Capacity = rs.getInt("capacity");
				f.CurrentPrice = rs.getFloat("bookingprice");
				f.FlightNumber = Integer.toString(f.FlightID);
				f.Reservations = null;
				
				vFlights.add(f);
			}
			conn.close();
		}
		catch(Exception e){
			System.err.println(e.toString());
		}

		Flight [] arrFlights = new Flight[vFlights.size()];
		vFlights.toArray(arrFlights);
		return arrFlights;
	}

	/**
	 * 
	 * This method is finished
	 * 
	 */
	public static Flight GetFlightDetails(int FlightID)
	{
		// TODO:  Query the database to get the flight information as well as all 
		// the reservations.
		
		Connection conn;
		Flight f = new Flight();
		try{
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, username, pass);
			//Statement s = conn.createStatement();
			//s.executeQuery("SELECT * FROM customers");
			
            PreparedStatement ps = conn.prepareStatement("select flightinfo.*, (select city from airport where iata=flightinfo.destid) as destcity, " +
            		"(select city from airport where iata=flightinfo.deptid) as deptcity, (allseatcount-bookedseats) as capacity, " +
            		"r.*, c.name, notes, if(date_diff > 14,baseprice, " +
            		"if(bookedseats/allseatcount >= 0.75, baseprice*2,if(bookedseats/allseatcount < 0.75 and bookedseats/allseatcount >= 0.5,baseprice,baseprice/2))) " +
            		"as bookingprice from (select f.*, count(seat) as allseatcount, datediff(deptdatetime,curdate()) as date_diff from seat_options s, flight_data f, plane p " +
            		"where s.planetype=p.planetype and p.planeid=f.planeid group by f.flightid) as flightinfo " +
            		"left join (select f.flightid as flightid, count(*) as bookedseats from reservation r, flight_data f, plane p, plane_types t " +
            		"where r.flightid=f.flightid and f.planeid=p.planeid and p.planetype=t.typeid group by flightid) as bookedtable " +
            		"on flightinfo.flightid=bookedtable.flightid join reservation r on flightinfo.flightid=r.flightid " +
            		"join customers c on c.custid=r.custid " +
            		"left join reservation_notes rn on rn.flightid=r.flightid and rn.custid=r.custid " +
            		"where r.flightid=?");
            ps.setInt(1,FlightID);
            ps.executeQuery();
            ResultSet rs = ps.getResultSet();
            if(rs.next()){
	            f.FlightID = rs.getInt("flightid");
	            f.ArrivalAirport = new Airport(rs.getString("DestId"),rs.getString("DestCity"));
	            f.DepartureAirport = new Airport(rs.getString("DeptId"),rs.getString("DeptCity"));
	            f.ArrivalTime = new Date(rs.getTimestamp("ArrDateTime").getTime());// rs.getTimestamp("ArrDateTime");
	            f.DepartureTime = new Date(rs.getTimestamp("DeptDateTime").getTime());
	            f.BasePrice = rs.getFloat("baseprice");
	            f.Capacity = rs.getInt("capacity");
	            f.CurrentPrice = rs.getFloat("bookingprice");
	            f.FlightNumber = Integer.toString(rs.getInt("flightid"));
	            
	            //move to top of results to loop over reservations
	            rs.beforeFirst();
	            Vector<Reservation> vReservations = new Vector<Reservation>();
	            Reservation r;
	            
	            while(rs.next()){
	            	r = new Reservation();
	            	r.Flight = f;
	            	r.MealOptions = rs.getString("meal");
	            	r.Seat = rs.getString("BookedSeat");
	            	r.Passenger = new Passenger();
	            	r.Passenger.Name = rs.getString("name");
	            	r.NotesAboutReservation = rs.getString("notes"); //need to fill this in
	            	r.PricePaid = rs.getDouble("BookedPrice");
	            	vReservations.add(r);
	            }
	            
	            Reservation [] arrReservations = new Reservation[vReservations.size()];
	    		vReservations.toArray(arrReservations);
	    		f.Reservations = arrReservations;
            }
		}
		catch(Exception e){
			System.err.println(e.toString());
		}
		/*
		// DUMMY DATA FOLLOWS
		Flight f = new Flight();
		f.FlightID = 1;	// ID from the DB..
		f.ArrivalAirport = new Airport(1,"Seattle");
		f.DepartureAirport = new Airport(1,"Portland");
		f.ArrivalTime = new Date();
		f.DepartureTime = new Date();
		f.BasePrice = 150;
		f.Capacity = 10;
		f.CurrentPrice = 300;
		f.FlightNumber = "642";
		
		Reservation r = new Reservation();
		r.Flight = f;
		r.MealOptions = "Steak";
		r.Seat = "14B";
		r.Passenger = new Passenger();
		r.Passenger.Name = "Kevin";
		r.NotesAboutReservation = "Has a baby.";
		r.PricePaid = 154;
		f.Reservations = new Reservation [] { r };
		*/
		return f;	
	}
	
	/**
	 * 
	 * This method is finished
	 * 
	 */
	public static Passenger [] GetCustomers ()
	{
		// TODO:  Query the database to retrieve a list of customers.
		
		Connection conn;
		Vector<Passenger> vPassengers = new Vector<Passenger>();
		try{
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, username, pass);
			Statement s = conn.createStatement();
			s.executeQuery("SELECT * FROM customers");
			ResultSet rs = s.getResultSet();
			
			Passenger p;
			
			while(rs.next()){
				p = new Passenger();
				p.Name = rs.getString("Name");
				p.PassengerID = rs.getInt("CustId");
				vPassengers.add(p);
			}
			conn.close();
		}
		catch(Exception e){
			System.err.println(e.toString());
		}
		// DUMMY VALUES FOLLOW
		/*Passenger a = new Passenger();
		a.Name = "Kevin";
		Passenger b = new Passenger();
		b.Name = "Niki";
		Passenger c = new Passenger();
		c.Name = "Ava";*/
		
		Passenger [] arrPassengers = new Passenger[vPassengers.size()];
		vPassengers.toArray(arrPassengers);
		return arrPassengers;
		//return new Passenger [] {a,b,c};
	}
	
	/**
	 * 
	 * This method is finished
	 * 
	 */
	public static Reservation [] GetCustomerRervations (Passenger p)
	{
		Vector<Reservation> vReservations = new Vector<Reservation>();
		Connection conn;
		try{
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, username, pass);
			//Statement s = conn.createStatement();
			//s.executeQuery("SELECT * FROM customers");
			
            PreparedStatement ps = conn.prepareStatement("select flightinfo.*, (select city from airport where iata=flightinfo.destid) as destcity, " +
            		"(select city from airport where iata=flightinfo.deptid) as deptcity, r.*, c.name, notes, if(date_diff > 14,baseprice, " +
            		"if(bookedseats/allseatcount >= 0.75, baseprice*2,if(bookedseats/allseatcount < 0.75 and bookedseats/allseatcount >= 0.5,baseprice,baseprice/2))) " +
            		"as bookingprice from (select f.*, count(seat) as allseatcount, datediff(deptdatetime,curdate()) as date_diff " +
            		"from seat_options s, flight_data f, plane p where s.planetype=p.planetype and p.planeid=f.planeid group by f.flightid) " +
            		"as flightinfo left join (select f.flightid as flightid, count(*) as bookedseats from " +
            		"reservation r, flight_data f, plane p, plane_types t where r.flightid=f.flightid and f.planeid=p.planeid and " +
            		"p.planetype=t.typeid group by flightid) as bookedtable on flightinfo.flightid=bookedtable.flightid " +
            		"join reservation r on flightinfo.flightid=r.flightid " +
            		"join customers c on c.custid=r.custid " +
            		"left join reservation_notes rn on rn.flightid=r.flightid and rn.custid=r.custid " +
            		"where c.CustId=?");
            ps.setInt(1,p.PassengerID);
            ps.executeQuery();
            ResultSet rs = ps.getResultSet();
			
			Reservation r;
			int fId;
			while(rs.next()){
				r = new Reservation();
				fId = rs.getInt("FlightId");
				r.Flight = new Flight();
				r.Flight.ArrivalAirport = new Airport(rs.getString("DestId"),rs.getString("DestCity"));
				r.Flight.DepartureAirport = new Airport(rs.getString("DeptId"),rs.getString("DeptCity"));
				r.Flight.ArrivalTime = new Date(rs.getTimestamp("ArrDateTime").getTime());
				r.Flight.DepartureTime = new Date(rs.getTimestamp("DeptDateTime").getTime());
				r.Flight.BasePrice = rs.getFloat("BasePrice");
				r.Flight.CurrentPrice = rs.getFloat("bookingprice");
				r.Flight.FlightNumber = Integer.toString(fId);
				r.MealOptions = rs.getString("Meal");
				r.Seat = rs.getString("BookedSeat");
				r.Passenger = new Passenger();
				r.Passenger.Name = rs.getString("name");
				r.NotesAboutReservation = rs.getString("notes");
				//r.NotesAboutReservation = "";
				r.PricePaid = rs.getDouble("bookedprice");
				//r.Name = rs.getString("Name");
				//r.PassengerID = rs.getInt("CustId");
				vReservations.add(r);
			}
			conn.close();
		}
		catch(Exception e){
			System.out.println(e.toString());
		}
		/*Reservation r = new Reservation();
		r.Flight = new Flight();
		r.Flight.ArrivalAirport = new Airport(0, "Seattle");
		r.Flight.DepartureAirport = new Airport(0, "Las Vegas");
		r.Flight.ArrivalTime = new Date();
		r.Flight.DepartureTime = new Date();
		r.Flight.BasePrice = 150;
		r.Flight.CurrentPrice = 300;
		r.Flight.FlightNumber = "154";
		r.MealOptions = "Steak";
		r.Seat = "14B";
		r.Passenger = new Passenger();
		r.Passenger.Name = "Kevin";
		r.NotesAboutReservation = "Has a baby.";
		r.PricePaid = 180;*/
		Reservation [] arrReservations = new Reservation[vReservations.size()];
		vReservations.toArray(arrReservations);
		return arrReservations;
		//return new Reservation [] { r };
	}
	
	/**
	 * 
	 * this method is finished
	 * 
	 */
	public static Reservation [] SearchReservationNotes(String query)
	{
		Vector<Reservation> vReservations = new Vector<Reservation>();
		Connection conn;
		try{
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, username, pass);
			//Statement s = conn.createStatement();
			//s.executeQuery("SELECT * FROM customers");
			
            PreparedStatement ps = conn.prepareStatement("select flightinfo.*, (select city from airport where iata=flightinfo.destid) as destcity, " +
            		"(select city from airport where iata=flightinfo.deptid) as deptcity, r.*, c.name, notes, MATCH(notes) AGAINST (?) AS relevance, " +
            		"if(date_diff > 14,baseprice, " +
            		"if(bookedseats/allseatcount >= 0.75, baseprice*2,if(bookedseats/allseatcount < 0.75 and bookedseats/allseatcount >= 0.5,baseprice,baseprice/2))) " +
            		"as bookingprice from (select f.*, count(seat) as allseatcount, datediff(deptdatetime,curdate()) as date_diff " +
            		"from seat_options s, flight_data f, plane p where s.planetype=p.planetype and p.planeid=f.planeid group by f.flightid) " +
            		"as flightinfo left join (select f.flightid as flightid, count(*) as bookedseats from " +
            		"reservation r, flight_data f, plane p, plane_types t where r.flightid=f.flightid and f.planeid=p.planeid and " +
            		"p.planetype=t.typeid group by flightid) as bookedtable on flightinfo.flightid=bookedtable.flightid " +
            		"join reservation r on flightinfo.flightid=r.flightid " +
            		"join customers c on c.custid=r.custid " +
            		"left join reservation_notes rn on rn.flightid=r.flightid and rn.custid=r.custid " +
            		"having relevance > 0 order by relevance desc");
            ps.setString(1, query );
            ps.executeQuery();
            ResultSet rs = ps.getResultSet();
            
			Reservation r;
			int fId;
			while(rs.next()){
				r = new Reservation();
				fId = rs.getInt("FlightId");
				r.Flight = new Flight();
				r.Flight.ArrivalAirport = new Airport(rs.getString("DestId"),rs.getString("DestCity"));
				r.Flight.DepartureAirport = new Airport(rs.getString("DeptId"),rs.getString("DeptCity"));
				r.Flight.ArrivalTime = new Date(rs.getTimestamp("ArrDateTime").getTime());
				r.Flight.DepartureTime = new Date(rs.getTimestamp("DeptDateTime").getTime());
				r.Flight.BasePrice = rs.getFloat("BasePrice");
				r.Flight.CurrentPrice = rs.getFloat("bookingprice");
				r.Flight.FlightNumber = Integer.toString(fId);
				r.MealOptions = rs.getString("Meal");
				r.Seat = rs.getString("BookedSeat");
				r.Passenger = new Passenger();
				r.Passenger.Name = rs.getString("name");
				r.NotesAboutReservation = rs.getString("notes");
				//r.NotesAboutReservation = "";
				r.PricePaid = rs.getDouble("bookedprice");
				r.Relavance = rs.getDouble("relevance");
				//r.Name = rs.getString("Name");
				//r.PassengerID = rs.getInt("CustId");
				vReservations.add(r);
			}
			conn.close();
		}
		catch(Exception e){
			System.out.println(e.toString());
		}
		Reservation [] arrReservations = new Reservation[vReservations.size()];
		vReservations.toArray(arrReservations);
		return arrReservations;
		/*
		Reservation r = new Reservation();
		r.Flight = new Flight();
		r.Flight.ArrivalAirport = new Airport(0, "Seattle");
		r.Flight.DepartureAirport = new Airport(0, "Las Vegas");
		r.Flight.ArrivalTime = new Date();
		r.Flight.DepartureTime = new Date();
		r.Flight.BasePrice = 150;
		r.Flight.CurrentPrice = 300;
		r.Flight.FlightNumber = "154";
		r.MealOptions = "Steak";
		r.Seat = "14B";
		r.Passenger = new Passenger();
		r.Passenger.Name = "Kevin";
		r.NotesAboutReservation = "Has a baby.";
		r.PricePaid = 180;
		r.Relavance = 0.7;
		return new Reservation [] { r };*/
	}
	                    
	public static void MakeReservation(Flight f, Passenger p, String Seat, String Meal, String Notes)
	{
		// TODO: Insert data into your database.
		// Show an error message if you can not make the reservation.
		
		//DONE
		
		Connection conn;
		try{
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, username, pass);
            conn.setAutoCommit(false);
	
		    PreparedStatement getAvailable = null;
		    PreparedStatement insertReservation = null;
		    PreparedStatement insertNotes = null;
		    int affectedRows = 0;

		   
		    try {
		    	//create the save point of the database before doing anything
		    	
		    	
		    	//availability query
		    	//if this returns any result, a booking is allowed
		    	//if no result: passenger already on board, no open seats, seat already booked, booking prices are different (old data), or seat not in plane range
		    	getAvailable = conn.prepareStatement("select flightinfo.*, r.*, if(isnull(bookedseats),allseatcount,allseatcount-bookedseats) " +
		    			"as openseats, if(date_diff > 14,baseprice, if(bookedseats/allseatcount >= 0.75, baseprice*2," +
		    			"if(bookedseats/allseatcount < 0.75 and bookedseats/allseatcount >= 0.5,baseprice,baseprice/2))) as bookingprice " +
		    			"from (select f.*, s.planetype, count(seat) as allseatcount, datediff(deptdatetime,curdate()) as date_diff " +
		    			"from seat_options s, flight_data f, plane p where s.planetype=p.planetype and p.planeid=f.planeid group by f.flightid) " +
		    			"as flightinfo left join (select f.flightid as flightid, count(*) as bookedseats from " +
		    			"reservation r, flight_data f, plane p, plane_types t where r.flightid=f.flightid and f.planeid=p.planeid and " +
		    			"p.planetype=t.typeid group by flightid) as bookedtable on flightinfo.flightid=bookedtable.flightid " +
		    			"join reservation r on r.flightid=flightinfo.flightid group by flightinfo.flightid " +
		    			"having flightinfo.flightid=? and openseats > 0 and ? not in (bookedseat) and ? not in (custid) and bookingprice=? " +
		    			"and ? in (select seat from seat_options so where so.planetype=flightinfo.planetype)");
		    	getAvailable.setInt(1,f.FlightID);
		    	getAvailable.setString(2,Seat);
		    	getAvailable.setString(5,Seat);
		    	getAvailable.setInt(3, p.PassengerID);
		    	getAvailable.setFloat(4, f.CurrentPrice);
		    	
		    	//from Java doc, .execute() returns:
		    	//true if the first result is a ResultSet object; false if the first result is an update count or there is no result 
				if (getAvailable.execute())
				{
				    //there are seats available
					insertReservation = conn.prepareStatement("INSERT INTO reservation " +
							"(flightid,custid,datebooked,meal,roundtrip,bookedprice,bookedseat) VALUES " +
							"(?,?,Now(),?,?,?,?)");
					insertReservation.setInt(1, f.FlightID);
					insertReservation.setInt(2, p.PassengerID);
					insertReservation.setString(3, Meal);
					insertReservation.setString(4, "N");
					insertReservation.setFloat(5, f.CurrentPrice);
					insertReservation.setString(6, Seat);
					affectedRows = insertReservation.executeUpdate();
					if(affectedRows == 0){
						throw new SQLException("Error on reservation insert");
					}
					
					insertNotes = conn.prepareStatement("INSERT INTO reservation_notes (flightid,custid,notes) " +
							"VALUES (?,?,?)");
					insertNotes.setInt(1, f.FlightID);
					insertNotes.setInt(2,p.PassengerID);
					insertNotes.setString(3, Notes );
					affectedRows = insertNotes.executeUpdate();
					if(affectedRows == 0){
						throw new SQLException("Error on note insert");
					}
					
					//inserted reservations and notes, so now commit
					conn.commit();
					
					//at this point, if the commit produced an error we would catch it and roll back
					//otherwise, tell them the booking was completed
					JOptionPane.showMessageDialog(null,
							"Reservation completed! Details: \n" + 
							"Flight: " + f.FlightNumber + "\n" + 
							"Passenger: " + p.Name + "\n" + 
							"Seat " + Seat + "\n" + 
							"Meal: " + Meal + "\n" + 
							"Notes: " + Notes);
				}
				else
				{
				    //booking not allowed
					throw new SQLException("Error in reservation");
				}
		    }
		    catch (SQLException e)
		    {
		    	//this catch is reached either by a connection problem, or not properly inserting data
		    	//roll the transaction back to the save point before starting queries and show error
		    	conn.rollback();
		    	JOptionPane.showMessageDialog(null, "Reservation NOT ACCEPTED. Details: \n" + 
		    			"Flight: " + f.FlightNumber + "\n" + 
						"Passenger: " + p.Name + "\n" + 
						"Seat " + Seat + "\n" + 
						"Meal: " + Meal + "\n" + 
						"Notes: " + Notes + "\n" + 
						"Possible problems: passenger already booked, seat not valid, seat already taken, old data (no more seats, price changed)");
			}
		    finally 
		    {
				  conn.setAutoCommit(true);
				  conn.close();
			}
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
		

		 
		
		/*JOptionPane.showMessageDialog(null, "Reservation on flight " + 
				f.FlightNumber + " for " + p.Name + " in seat " + 
				Seat + " eating " + Meal + " and with notes: " + Notes);
		*/
	}
	
	//This method is called if there is a parse error in the search date of the main window
	//it will return all the flights in the database regardless of search date and dept/arrive
	public static Flight [] GetAllFlights(){
		Vector<Flight> vFlights = new Vector<Flight>();
		Connection conn;
		Flight f;
		try{
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, username, pass);
			PreparedStatement ps = conn.prepareStatement("select fd.*, (select city from airport where iata=fd.destid) as destcity, " +
					"(select city from airport where iata=fd.deptid) as deptcity, " +
					"capacity, bookingprice from flight_data fd " +
					"join (select flightinfo.flightid, (allseatcount-bookedseats) as capacity, " +
					"if(isnull(bookedseats/allseatcount),0,(bookedseats/allseatcount)) as booked_percent, baseprice, date_diff, " +
					"if(date_diff > 14,baseprice, " +
					"if(bookedseats/allseatcount >= 0.75, baseprice*2,if(bookedseats/allseatcount < 0.75 and bookedseats/allseatcount >= 0.5,baseprice,baseprice/2))) " +
					"as bookingprice from " +
					"(select f.flightid as flightid, count(seat) as allseatcount, baseprice, datediff(deptdatetime,curdate()) as date_diff " +
					"from seat_options s, flight_data f, plane p where s.planetype=p.planetype and p.planeid=f.planeid group by f.flightid) as flightinfo " +
					"left join (select f.flightid as flightid, count(*) as bookedseats from reservation r, flight_data f, plane p, plane_types t " +
					"where r.flightid=f.flightid and f.planeid=p.planeid and p.planetype=t.typeid group by flightid) as bookedtable " +
					"on flightinfo.flightid=bookedtable.flightid) as booking_info on booking_info.flightid=fd.flightid " +
					"order by deptdatetime");
			
			ps.executeQuery();
			ResultSet rs = ps.getResultSet();
			while(rs.next()){
				
				f = new Flight();
				f.FlightID = rs.getInt("FlightId");
				f.ArrivalAirport = new Airport(rs.getString("DestId"),rs.getString("DestCity"));
				f.DepartureAirport = new Airport(rs.getString("DeptId"),rs.getString("DeptCity"));
				f.ArrivalTime = new Date(rs.getTimestamp("ArrDateTime").getTime());
				f.DepartureTime = new Date(rs.getTimestamp("DeptDateTime").getTime());
				f.BasePrice = rs.getFloat("BasePrice");
				f.Capacity = rs.getInt("capacity");
				f.CurrentPrice = rs.getFloat("bookingprice");
				f.FlightNumber = Integer.toString(f.FlightID);
				f.Reservations = null;
				
				vFlights.add(f);
			}
			conn.close();
		}
		catch(Exception e){
			System.err.println(e.toString());
		}

		Flight [] arrFlights = new Flight[vFlights.size()];
		vFlights.toArray(arrFlights);
		return arrFlights;
	
	}
}
